"""
Case Type   : cm
Case Name   : 同AZ集群模式下，关闭最大可用模式，同步模式为on，alter system set方式设置同步备列表为ANY1(N2),ANY1(N3),ANY1(N4),ANY1(N5)
Create At   : 2024-07
Owner       : haomeng
Description :
    1、查看集群状态
    2、查看最大可用模式及同步模式，设置同步备列表为ANY1(N2),ANY1(N3),ANY1(N4),ANY1(N5)
    3、主节点执行事务
    4、停止分组1中备节点，查看数据库集群状态，并观察主节点事务执行情况
    5、恢复拉起分组1中停掉的备节点
    6、停止分组2中备节点，查看数据库集群状态，并观察主节点事务执行情况
    7、恢复拉起分组2中停掉的备节点
    8、停止分组3中备节点，查看数据库集群状态，并观察主节点事务执行情况
    9、恢复拉起分组3中停掉的备节点
    10、停止分组4中备节点，查看数据库集群状态，并观察主节点事务执行情况
    11、恢复拉起分组4中停掉的备节点
    12、同时停止任意三个分组中备节点，查看数据库集群状态，并观察主节点事务执行情况
    13、恢复拉起任意两个分组中停掉的节点，查看数据库集群状态，观察主节点事务执行情况
    14、再次恢复另一分组中的异常节点，查看数据库集群状态，观察主节点事务执行情况
    15、停止所有分组中备节点，查看数据库集群状态，并观察主节点事务执行情况
    16、恢复分组中任意三个备节点，查看数据库集群状态，并观察主节点事务执行情况
    17、再次恢复另一分组中的异常节点，查看数据库集群状态，观察主节点事务执行情况
    18、恢复参数默认值，恢复集群初始状态
Expect      :
    1、集群所有节点状态正常，显示Normal
    2、最大可用模式为off，同步模式为on，设置同步备参数成功
    3、主节点执行事务成功
    4、停止成功，被停止备节点stopped，主节点执行事务不阻塞
    5、拉起成功，节点状态Normal
    6、停止成功，被停止备节点stopped，主节点执行事务阻塞
    7、拉起成功，节点状态Normal
    8、停止成功，被停止备节点stopped，主节点执行事务阻塞
    9、拉起成功，节点状态Normal
    10、停止成功，被停止备节点stopped，主节点执行事务阻塞
    11、拉起成功，节点状态Normal
    12、停止成功，被停止节点stopped，主节点执行事务阻塞
    13、拉起成功，节点状态Normal，主节点执行事务阻塞
    14、拉起成功，节点状态Normal，主节点执行事务恢复不阻塞
    15、停止成功，被停止节点stopped，主节点执行事务阻塞
    16、拉起成功，节点状态Normal，主节点执行事务阻塞
    17、拉起成功，节点状态Normal，主节点执行事务恢复不阻塞
    18、恢复成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(5 != Primary_SH.get_node_num(), '需1主4备环境，否则不执行')
class CmCtl(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.comsh = CommonSH('PrimaryDbUser')
        self.com = Common('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.sync_commit = 'synchronous_commit'
        self.available = 'most_available_sync'
        self.sync_standby_name = 'synchronous_standby_names'
        self.table = 't_anyx_009'

    def test_cm(self):
        text = '----step1:查看集群状态; expect:集群状态正常,DN主在节点1----'
        self.log.info(text)

        status = self.comsh.exec_cm_ctl('query', '-Cv', '-z ALL')
        self.log.info(status)
        az = status.splitlines()[-1]
        self.assertTrue('AZ1' in az and 'AZ2' in az, '集群状态异常')

        text = '----step2:查看最大可用模式及同步模式，设置同步备列表为ANY1(N2),ANY 1(N3),ANY1(N4),ANY1(N5);' \
               ' expect:最大可用模式为off，同步模式为on，设置同步备参数成功----'
        self.log.info(text)

        self.default_sync_commit = self.com.show_param(self.sync_commit)
        self.log.info(self.default_sync_commit)
        if self.default_sync_commit != 'on':
            set1 = self.comsh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.sync_commit} = on')
            self.log.info(set1)

        self.default_available = self.com.show_param(self.available)
        self.log.info(self.default_available)
        if self.default_available != 'off':
            set2 = self.comsh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.available} = off')
            self.log.info(set2)

        self.default_sync_standby_name = self.com.show_param(self.sync_standby_name)
        self.log.info(self.default_sync_standby_name)
        sql = f"alter system set {self.sync_standby_name} " \
              f"to 'ANY 1(N2), ANY 1(N3), ANY 1(N4), ANY 1(N5)';"
        self.log.info(sql)
        set3 = self.comsh.execut_db_sql(sql)
        self.log.info(set3)
        self.assertIn(self.constant.ALTER_SYSTEM_SUCCESS_MSG, set3, '参数设置失败')

        text = '------step3:主节点执行事务; expect:主节点执行事务成功------'
        self.log.info(text)

        sql_cmd= f"create table {self.table}(id int,name varchar(100));" \
                 f"insert into {self.table} values generate_series(1,1000)," \
                 f"'name_'||generate_series(1,1000);" \
                 f"update {self.table} set name='cm' where id <666;" \
                 f"delete from {self.table} where id > 123;" \
                 f"select count(*) from {self.table};" \
                 f"drop table {self.table} cascade;"
        self.log.info(sql_cmd)

        for i in range(100):
            self.sql_res = ComThread(self.comsh.execut_db_sql, args=(sql_cmd,))
            self.sql_res.setDaemon(True)
            self.sql_res.start()

        text = '-----step4:停止分组1中备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:停止成功，被停止备节点stopped，主节点执行事务不阻塞-----'
        self.log.info(text)

        stop = f'stop -n 2 -D {macro.DB_INSTANCE_PATH}'
        stop_res = ComThread(self.comsh.exec_cm_ctl, args=(stop,))
        stop_res.setDaemon(True)
        stop_res.start()

        sql_result = self.sql_res.get_result()
        self.log.info(sql_result)
        count_res1 = sql_result.splitlines()[-3].strip()
        self.log.info(count_res1)
        stop_result = stop_res.result()
        self.log.info(stop_result)
        self.assertIn(self.constant.cm_stop_instance_success_msg, stop_result, '停止节点失败')

        count_res2 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res2)
        self.assertEqual(count_res1, count_res2.splitlines()[-2], '事务未阻塞')

        text = '-----step5:拉起恢复分组1中备节点; expect:拉起成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 2 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起失败')

        status = self.comsh.exec_cm_ctl('query', '-Cv')
        self.log.info(status)
        self.assertIn("cluster_state   : Normal", status, '状态异常')

        text = '-----step6:停止分组2中备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:停止成功，被停止备节点stopped，主节点执行事务阻塞-----'
        self.log.info(text)

        stop = f'stop -n 3 -D {macro.DB_INSTANCE_PATH}'
        stop_res =self.comsh.exec_cm_ctl(stop)
        self.log.info(stop_res)
        self.log.info(self.constant.cm_stop_instance_success_msg, stop_res, '停止节点失败')

        count_res3 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res3)
        self.assertEqual(count_res2.splitlines()[-2], count_res3.splitlines()[-2], '事务未阻塞')

        text = '-----step7:拉起恢复分组2中备节点，查看数据库集群状态;' \
               'expect:拉起成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 5 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        text = '-----step8:停止分组3中备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:停止成功，被停止备节点stopped，主节点执行事务阻塞-----'
        self.log.info(text)

        stop = f'stop -n 4 -D {macro.DB_INSTANCE_PATH}'
        stop_res =self.comsh.exec_cm_ctl(stop)
        self.log.info(stop_res)
        self.log.info(self.constant.cm_stop_instance_success_msg, stop_res, '停止节点失败')

        count_res4 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res4)
        self.assertEqual(count_res3.splitlines()[-2], count_res4.splitlines()[-2], '事务未阻塞')

        text = '-----step9:拉起恢复分组3中备节点，查看数据库集群状态;' \
               'expect:拉起成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 4 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        text = '-----step10:停止分组4中备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:停止成功，被停止备节点stopped，主节点执行事务阻塞-----'
        self.log.info(text)

        stop = f'stop -n 5 -D {macro.DB_INSTANCE_PATH}'
        stop_res =self.comsh.exec_cm_ctl(stop)
        self.log.info(stop_res)
        self.log.info(self.constant.cm_stop_instance_success_msg, stop_res, '停止节点失败')

        count_res5 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res5)
        self.assertEqual(count_res4.splitlines()[-2], count_res5.splitlines()[-2], '事务未阻塞')

        text = '-----step11:拉起恢复分组3中备节点，查看数据库集群状态;' \
               'expect:拉起成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 5 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        text = '-----step12:同时停止任意三个分组中备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:停止成功，被停止备节点stopped，主节点执行事务不阻塞-----'
        self.log.info(text)

        stop = f'stop -n 2 -D {macro.DB_INSTANCE_PATH};' \
               f'cm_ctl stop -n 3 -D {macro.DB_INSTANCE_PATH};' \
               f'cm_ctl stop -n 4 -D {macro.DB_INSTANCE_PATH};'
        stop_res = self.comsh.exec_cm_ctl(stop)
        self.log.info(stop_res)
        self.assertIn(self.constant.cm_stop_instance_success_msg, stop_res, '停止节点失败')

        count_res6 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res6)
        self.assertEqual(count_res5.splitlines()[-2], count_res6.splitlines()[-2], '事务未阻塞')

        text = '-----step13:拉起恢复任意两个分组中被停止备节点，查看数据库集群状态;' \
               'expect:恢复成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 2 -D {macro.DB_INSTANCE_PATH};' \
                f'cm_ctl start -n 3 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        count_res7 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res7)
        self.assertEqual(count_res6.splitlines()[-2], count_res7.splitlines()[-2], '事务未阻塞')

        text = '-----step14:再次拉起恢复另一分组中被停止备节点，查看数据库集群状态;' \
               'expect:恢复成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 4 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        count_res8 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res8)
        self.assertEqual(count_res7.splitlines()[-2], count_res8.splitlines()[-2], '事务未阻塞')

        text = '-----step15:停止所有分组中所有备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:停止成功，被停止备节点stopped，主节点执行事务阻塞-----'
        self.log.info(text)

        stop = f'stop -n 2 -D {macro.DB_INSTANCE_PATH};' \
               f'cm_ctl stop -n 3 -D {macro.DB_INSTANCE_PATH};' \
               f'cm_ctl stop -n 4 -D {macro.DB_INSTANCE_PATH};' \
               f'cm_ctl stop -n 5 -D {macro.DB_INSTNACE_PATH};'
        stop_res = self.comsh.exec_cm_ctl(stop)
        self.log.info(stop_res)
        self.assertIn(self.constant.cm_stop_instance_success_msg, stop_res, '停止节点失败')

        count_res9 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res9)
        self.assertEqual(count_res8.splitlines()[-2], count_res9.splitlines()[-2], '事务未阻塞')

        text = '-----step16:拉起恢复任意3个分组中备节点，查看数据库集群状态，并观察主节点事务执行情况;' \
               'expect:拉起成功，被停止备节点Normal，主节点执行事务不阻塞-----'
        self.log.info(text)

        start = f'start -n 2 -D {macro.DB_INSTANCE_PATH};' \
                f'cm_ctl start -n 3 -D {macro.DB_INSTANCE_PATH};' \
                f'cm_ctl start -n 4 -D {macro.DB_INSTANCE_PATH};'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        count_res10 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res10)
        self.assertEqual(count_res9.splitlines()[-2], count_res10.splitlines()[-2], '事务未阻塞')

        text = '-----step17:再次拉起恢复剩余分组中被停止备节点，查看数据库集群状态;' \
               'expect:恢复成功，被停止备节点Normal-----'
        self.log.info(text)

        start = f'start -n 5 -D {macro.DB_INSTANCE_PATH}'
        start_res = self.comsh.exec_cm_ctl(start)
        self.log.info(start_res)
        self.assertIn(self.constant.cm_start_instance_success_msg, start_res, '拉起节点失败')

        count_res11 = self.comsh.execut_db_sql(f'select count(*) from {self.table};')
        self.log.info(count_res11)
        self.assertNotEqual(count_res10.splitlines()[-2], count_res11.splitlines()[-2], '事务阻塞')

    def tearDown(self):
        text = '-----step18:恢复参数默认值，恢复集群状态  expect:恢复成功-----'
        self.log.info(text)

        sync_commit_param = self.com.show_param(self.sync_commit)
        if sync_commit_param != self.default_sync_commit:
            reset1 = self.comsh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.sync_commit} = {self.default_sync_commit}')
            self.log.info(reset1)

        available_param = self.com.show_param(self.available)
        if available_param != self.default_available:
            reset2 = self.comsh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.available} = {self.default_available}')
            self.log.info(reset2)

        reset3 = self.comsh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f'{self.sync_standby_name}={self.default_sync_standby_name}')
        self.log.info(reset3)

        restart = self.comsh.exec_cm_ctl('stop;', 'cm_ctl start')
        self.log.info(restart)

        status = self.comsh.exec_cm_ctl('query', '-Cv')
        self.log.info(status)
        p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
        if 'Primary' in p_status:
            self.log.info('---集群状态正常，无需恢复为初始状态---')
        else:
            recovery = Primary_SH.exec_cm_ctl(mode='switchover', param='-a')
            self.log.info(recovery)

            self.assertIn(self.constant.cm_switchover_success_msg, recovery,
                          '切换失败')

        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cv')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '状态异常')
        self.assertIn('P Primary Normal',
                      status.splitlines()[-1].split('|')[0].split('6001')[-1],
                      '状态异常')
        self.assertIn('S Standby Normal',
                      status.splitlines()[-1].split('|')[1].split('6002')[-1],
                      '状态异常')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
